import datetime
import io
import json
import os
import urllib.parse
from email.utils import parsedate_to_datetime

import pytz
import requests
from flask import Blueprint, request, jsonify, Response, send_file
import pandas as pd

from dbs.sqlserver1 import db1_execute_query
from methods.chromePlugins.tmall import fetch_and_adjust_image, resize_and_crop_image
from utils.tools import extract_substring, send_excel_template
from flask import make_response

bp = Blueprint("chromePluginsTmall", __name__, url_prefix="/chromePluginsTmall")


@bp.route('/returnExcelContent', methods=['post'])
def returnExcelContent():
    # 1、判断是否上传文件以及必填参数
    if 'file' not in request.files:
        return jsonify({'message': '请上传文件！', 'code': 400})

    file = request.files['file']

    if file.filename == '' or '.' not in file.filename or file.filename.rsplit('.', 1)[1].lower() not in {'xlsx'}:
        return jsonify({'message': '请上传正确的Excel文件！', 'code': 400})

    filename = datetime.datetime.now().strftime('%Y%m%d%H%M%S') + '_' + file.filename
    with open(f"./tmp/{filename}", mode="wb") as f:
        f.write(file.stream.read())
        print(f"{filename}保存完毕！")
    # 1-2 读取文件内容
    excel_data = []
    try:
        df = pd.read_excel(f"./tmp/{filename}")
        header = df.columns.tolist()
        print(header)
        if header != ['书号', '定价', '商家编码', '折扣', '售价', '库存', '运费模版']:
            return {'message': 'Excel文件内容不正确！', 'code': 400}
        book_ids = []
        for index, row in df.iterrows():
            orig_isbn = str(row['书号'])
            isbn = extract_substring(orig_isbn)
            if isbn:
                row_data = {
                    'isbn': isbn.strip(),
                    'price': str(row['定价']).strip(),
                    'book_id': row['商家编码'].strip(),
                    'discount': str(row['折扣']).strip() if not pd.isna(row['折扣']) and row['折扣'] != '' else '',
                    # 判断售价是否为空
                    'sale_price': str(row['售价']).strip() if not pd.isna(row['售价']) and row['售价'] != '' else '',
                    'stock': str(row['库存']).strip(),
                    'templates': str(row['运费模版']).strip(),
                }
                book_ids.append(str(row_data['book_id'].strip()))
                excel_data.append(row_data)
        json_data = {
            'shopname': '上海世纪出版官方旗舰店',
            'bookids': book_ids,
        }
        print(json_data)
        o_response = requests.post('https://open.cloudycentury.net:89/ccplugins/tmbook', json=json_data)
        o_response_json = json.loads(o_response.text)
        print(o_response_json)
        print(len(o_response_json['data']['bookids']))
        print(o_response_json['status'] == 200)
        if o_response_json['status'] == 200:
            if len(o_response_json['data']['bookids']) == 0:
                excel_data = []
            else:
                new_excel_data = []
                for elem in excel_data:
                    if str(elem.get('book_id')) in o_response_json['data']['bookids']:
                        new_excel_data.append(elem)
                excel_data = new_excel_data
        data = {
            '书号': [],
            '定价': [],
            '图书标签': [],
            '商家编码': [],
            '折扣': [],
            '售价': [],
            '库存': [],
            '标品/达尔文ID': [],
            '类目': [],
            '商品标题': [],
            '品牌(京东店铺分类用,不填系统帮选)': [],
            '运费模版': [],
            'selfBelowDeptIds': [],
        }
        if len(excel_data) != 0:
            # 循环excel_data
            for index, elem in enumerate(excel_data):
                if elem.get('isbn') is None or elem.get('price') is None or elem.get('book_id') is None or elem.get(
                        'discount') is None or elem.get('stock') is None:
                    return jsonify({'message': f'第{index + 1}行数据不完整！', 'code': 400})

                data['书号'].append(elem.get('isbn'))
                data['定价'].append(elem.get('price'))
                data['图书标签'].append(elem.get('tags', ''))
                data['商家编码'].append(elem.get('book_id'))
                data['折扣'].append(elem.get('discount'))
                data['售价'].append(elem.get('sale_price', ''))
                data['库存'].append(elem.get('stock'))
                data['标品/达尔文ID'].append(elem.get('standard_id', ''))
                data['类目'].append(elem.get('category', ''))
                data['商品标题'].append(elem.get('title', ''))
                data['品牌(京东店铺分类用,不填系统帮选)'].append(elem.get('brand', ''))
                data['运费模版'].append(elem.get('templates', ''))
                data['selfBelowDeptIds'].append(elem.get('self_below_dept_ids', ''))
        df = pd.DataFrame(data)

        # 将DataFrame写入到一个Excel文件的内存中
        output = io.BytesIO()
        with pd.ExcelWriter(output, engine='openpyxl') as writer:
            df.to_excel(writer, index=False, sheet_name='Sheet1')

        # 设置文件指针到开始位置
        output.seek(0)

        # 创建一个响应对象并发送文件
        response = make_response(output.read())
        response.headers['Content-Disposition'] = 'attachment; filename=format_data.xlsx'
        response.headers['Content-type'] = 'application/vnd.openxmlformats-officedocument.spreadsheetml.sheet'
        return response
    except Exception as e:
        return {'message': f'读取Excel文件出错！错误原因：{e}', 'code': 500}, 500
    finally:
        os.remove(f"./tmp/{filename}")


@bp.route('/getReturnExcelContent', methods=['get'])
def getReturnExcelContent():
    data = {
        '书号': [],
        '定价': [],
        '商家编码': [],
        '折扣': [],
        '售价': [],
        '库存': [],
        '运费模版': [],
    }
    response = send_excel_template(data, '数据去重模板')
    return response


@bp.route('/generate_excel', methods=['post'])
def generate_excel():
    pass
    # 1. 判断是否上传文件以及必填参数
    # params = request.get_json()
    # data = {
    #     '书号': [],
    #     '定价': [],
    #     '图书标签': [],
    #     '商家编码': [],
    #     '折扣': [],
    #     '售价': [],
    #     '库存': [],
    #     '标品/达尔文ID': [],
    #     '类目': [],
    #     '商品标题': [],
    #     '品牌(京东店铺分类用,不填系统帮选)': [],
    #     'selfBelowDeptIds': [],
    # }
    #
    # if params is None:
    #     return jsonify({'message': '请上传数据！', 'code': 400})
    #
    # excel_data = params.get('excel_data')
    # if excel_data is None:
    #     return jsonify({'message': '请上传数据！', 'code': 400})
    #
    # # 循环excel_data
    # for index, elem in enumerate(excel_data):
    #     if elem.get('isbn') is None or elem.get('price') is None or elem.get('book_id') is None or elem.get(
    #             'discount') is None or elem.get('stock') is None:
    #         return jsonify({'message': f'第{index + 1}行数据不完整！', 'code': 400})
    #
    #     data['书号'].append(elem.get('isbn'))
    #     data['定价'].append(elem.get('price'))
    #     data['图书标签'].append(elem.get('tags', ''))
    #     data['商家编码'].append(elem.get('book_id'))
    #     data['折扣'].append(elem.get('discount'))
    #     data['售价'].append(elem.get('sale_price', ''))
    #     data['库存'].append(elem.get('stock'))
    #     data['标品/达尔文ID'].append(elem.get('standard_id', ''))
    #     data['类目'].append(elem.get('category', ''))
    #     data['商品标题'].append(elem.get('title', ''))
    #     data['品牌(京东店铺分类用,不填系统帮选)'].append(elem.get('brand', ''))
    #     data['selfBelowDeptIds'].append(elem.get('self_below_dept_ids', ''))
    #
    # df = pd.DataFrame(data)
    #
    # # 将DataFrame写入到一个Excel文件的内存中
    # output = io.BytesIO()
    # with pd.ExcelWriter(output, engine='openpyxl') as writer:
    #     df.to_excel(writer, index=False, sheet_name='Sheet1')
    #
    # # 设置文件指针到开始位置
    # output.seek(0)
    #
    # # 创建一个响应对象并发送文件
    # response = make_response(output.read())
    # response.headers['Content-Disposition'] = 'attachment; filename=format_data.xlsx'
    # response.headers['Content-type'] = 'application/vnd.openxmlformats-officedocument.spreadsheetml.sheet'
    # return response


@bp.route('/tmallToDcc', methods=['post'])
def tmallToDcc():
    data = request.get_json()
    if data is None:
        return {'code': 0, 'message': '请传入数据'}
    if data.get('isbn') is None:
        return {'code': 0, 'message': '请传入ISBN'}

    print(data)
    return data


# 传入url，自动转图片为20kb-500kb之间的二进制流数据
@bp.route('/formatFileToStream', methods=['post'])
def formatFileToStream():
    url = request.get_json().get('url')
    if url is None:
        return {'code': 0, 'message': '请传入数据'}
    result = fetch_and_adjust_image(url)
    return result


# 传入url，自动转图片为20kb-500kb之间的数据
@bp.route('/formatFileToStreamGet', methods=['get'])
def formatFileToStreamGet():
    url = request.args.get('url')
    if url is None:
        return {'code': 0, 'message': '请传入数据'}
    result = fetch_and_adjust_image(url)
    file_stream = io.BytesIO(result)
    file_name_format = url.split('.')[-1]
    response = Response(file_stream, content_type='application/octet-stream')
    response.headers['Content-Disposition'] = f'attachment; filename=deal_f.{file_name_format}'
    return response


@bp.route('/getRateExcelContent', methods=['get'])
def getRateExcelContent():
    data = {
        '平台': [],
        '店铺': [],
        '商品ID': [],
        '税率': [],
    }
    response = send_excel_template(data, '设置税率模板')
    return response


@bp.route('/test_connection', methods=['get'])
def test_connection():
    sql = "select outerId,skuBarcode,skuName from jdValidSku left join ErpStorageGood on jdValidSku.outerId=ErpStorageGood.goodsNo where jdValidSku.availability=1 and wareId='10021649839144';"
    results = db1_execute_query(sql)
    if results:
        for row in results:
            print(row)
        # 将结果转换为 JSON 格式返回
        return jsonify(results)
    else:
        return jsonify({"error": "No results found"}), 404


# 传入url，自动转图片为800*800流文件
@bp.route('/transformPicture', methods=['post'])
def transformPicture():
    data = request.get_json()
    if data is None:
        return {'code': 0, 'message': '请传入数据'}
    if data.get('url') is None:
        return {'code': 0, 'message': '请传入图片地址'}
    print(data)
    return resize_and_crop_image(data.get('url'))


# 传入url，自动下载图片800*800文件
@bp.route('/downloadTransformPicture', methods=['get'])
def downloadTransformPicture():
    url = request.args.get('url')
    if url is None:
        return {'code': 0, 'message': '请传入图片地址'}
    image_stream = resize_and_crop_image(url)
    return send_file(image_stream, mimetype='image/jpeg', as_attachment=True, download_name='transformed_image.jpg')


# 更新谷歌插件的操作
@bp.route('/updateChromePlugin', methods=['post'])
def updateChromePlugin():
    # 定义保存文件的目录
    upload_folder = request.form.get('operate_folder_path') or 'files/googlePlugin'
    operate_name = request.form.get('operate_name') or 'google_plugin'
    os.makedirs(upload_folder, exist_ok=True)
    if 'file' not in request.files:
        return jsonify({'error': 'No file part'}), 400

    file = request.files['file']
    data = request.form.get('data')

    if file.filename == '':
        return jsonify({'error': 'No selected file'}), 400

    # 确保文件是zip或crx格式
    if not (file.filename.endswith('.zip') or file.filename.endswith('.crx')):
        return jsonify({'error': 'File must be a zip or crx'}), 400

    # 根据文件类型决定新的文件名
    if file.filename.endswith('.zip'):
        new_filename = operate_name + '.zip'
        old_filename = os.path.join(upload_folder, operate_name + '.crx')
    else:  # 文件类型为 .crx
        new_filename = operate_name + '.crx'
        old_filename = os.path.join(upload_folder, operate_name + '.zip')

    # 如果旧文件存在，则删除它
    if os.path.exists(old_filename):
        os.remove(old_filename)

    # 保存新文件
    file_path = os.path.join(upload_folder, new_filename)
    file.save(file_path)

    # 写入updateLog.txt文件
    log_file_path = os.path.join(upload_folder, 'updateLog.txt')

    with open(log_file_path, 'w') as log_file:
        log_file.write(data + '\n')

    return jsonify({'message': 'File uploaded successfully'}), 200


# 读取谷歌插件版本号数据
@bp.route('/getChromePluginVersion', methods=['get'])
def getChromePluginVersion():
    upload_folder = request.args.get('operate_folder_path') or 'files/googlePlugin'
    log_file_path = os.path.join(upload_folder, 'updateLog.txt')

    # 检查文件是否存在
    if not os.path.isfile(log_file_path):
        return jsonify({'code': 404, 'message': 'Log file not found.'})

    with open(log_file_path, 'r', encoding='utf-8') as log_file:
        # 读取整个文件内容
        content = log_file.read().strip()

        try:
            # 尝试解析 JSON 内容
            data = json.loads(content)
            # version = data.get('version', 'unknown version')
            # message = data.get('version', 'unknown message')
        except json.JSONDecodeError:
            return jsonify({'code': 400, 'message': 'Invalid JSON format.'})
    return jsonify({'code': 200, 'data': data})

# 下载谷歌插件
@bp.route('/getChromePluginFile', methods=['get'])
def getChromePluginFile():
    upload_folder = request.args.get('operate_folder_path') or 'files/googlePlugin'
    operate_name = request.args.get('operate_name') or 'google_plugin'

    zip_file_path = os.path.join(upload_folder, operate_name + '.zip')
    crx_file_path = os.path.join(upload_folder, operate_name + '.crx')

    # 检查文件是否存在并返回相应的文件
    if os.path.isfile(zip_file_path):
        return send_file(zip_file_path, mimetype='application/zip', as_attachment=True,
                         download_name=operate_name + '.zip')
    elif os.path.isfile(crx_file_path):
        return send_file(crx_file_path, mimetype='application/x-chrome-extension', as_attachment=True,
                         download_name=operate_name + '.crx')
    else:
        return jsonify({'error': 'File not found'}), 404
